{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cx = sqlite3.connect(\"D:/vnpy/.vntrader/database.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "cu=cx.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'EG2201', 'DCE', '1m', 67785, '2021-01-26 21:00:00', '2021-11-24 10:14:00'), (2, 'EG2201', 'DCE', 'd', 194, '2021-01-27 00:00:00', '2021-11-17 00:00:00'), (3, 'MA201', 'CZCE', 'd', 201, '2021-01-18 00:00:00', '2021-11-17 00:00:00'), (4, 'MA201', 'CZCE', '1m', 70200, '2021-01-15 21:00:00', '2021-11-24 10:14:00'), (5, 'MA888', 'CZCE', '1m', 250065, '2018-11-16 21:00:00', '2021-11-22 14:59:00'), (6, 'RM201', 'CZCE', '1m', 69795, '2021-01-15 21:00:00', '2021-11-23 09:14:00')]\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect(\"D:/vnpy/.vntrader/database.db\")\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"select * from dbbaroverview\")\n",
    "values =  cursor.fetchall()\n",
    "cursor.close()\n",
    "conn.close()\n",
    "print(values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "values = cu.fetchall()\n",
    "print(values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x204386972d0>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cu.execute(\"create table catalog (id integer primary key,pid integer,name varchar(10) UNIQUE,nickname text NULL)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(0, 'id', 'INTEGER', 1, None, 1), (1, 'symbol', 'VARCHAR(255)', 1, None, 0), (2, 'exchange', 'VARCHAR(255)', 1, None, 0), (3, 'datetime', 'DATETIME', 1, None, 0), (4, 'name', 'VARCHAR(255)', 1, None, 0), (5, 'volume', 'REAL', 1, None, 0), (6, 'turnover', 'REAL', 1, None, 0), (7, 'open_interest', 'REAL', 1, None, 0), (8, 'last_price', 'REAL', 1, None, 0), (9, 'last_volume', 'REAL', 1, None, 0), (10, 'limit_up', 'REAL', 1, None, 0), (11, 'limit_down', 'REAL', 1, None, 0), (12, 'open_price', 'REAL', 1, None, 0), (13, 'high_price', 'REAL', 1, None, 0), (14, 'low_price', 'REAL', 1, None, 0), (15, 'pre_close', 'REAL', 1, None, 0), (16, 'bid_price_1', 'REAL', 1, None, 0), (17, 'bid_price_2', 'REAL', 0, None, 0), (18, 'bid_price_3', 'REAL', 0, None, 0), (19, 'bid_price_4', 'REAL', 0, None, 0), (20, 'bid_price_5', 'REAL', 0, None, 0), (21, 'ask_price_1', 'REAL', 1, None, 0), (22, 'ask_price_2', 'REAL', 0, None, 0), (23, 'ask_price_3', 'REAL', 0, None, 0), (24, 'ask_price_4', 'REAL', 0, None, 0), (25, 'ask_price_5', 'REAL', 0, None, 0), (26, 'bid_volume_1', 'REAL', 1, None, 0), (27, 'bid_volume_2', 'REAL', 0, None, 0), (28, 'bid_volume_3', 'REAL', 0, None, 0), (29, 'bid_volume_4', 'REAL', 0, None, 0), (30, 'bid_volume_5', 'REAL', 0, None, 0), (31, 'ask_volume_1', 'REAL', 1, None, 0), (32, 'ask_volume_2', 'REAL', 0, None, 0), (33, 'ask_volume_3', 'REAL', 0, None, 0), (34, 'ask_volume_4', 'REAL', 0, None, 0), (35, 'ask_volume_5', 'REAL', 0, None, 0), (36, 'localtime', 'DATETIME', 0, None, 0)]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "conn = sqlite3.connect(\"D:/vnpy/.vntrader/database.db\")\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('PRAGMA table_info(dbtickdata)')\n",
    "values =  cursor.fetchall()\n",
    "cursor.close()\n",
    "conn.close()\n",
    "print(values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<sqlite3.Cursor object at 0x000002043923AF80>\n"
     ]
    }
   ],
   "source": [
    "print(values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "EG2201\n",
      "DCE\n",
      "1m\n",
      "67785\n",
      "2021-01-26 21:00:00\n",
      "2021-11-24 10:14:00\n",
      "2\n",
      "EG2201\n",
      "DCE\n",
      "d\n",
      "194\n",
      "2021-01-27 00:00:00\n",
      "2021-11-17 00:00:00\n",
      "3\n",
      "MA201\n",
      "CZCE\n",
      "d\n",
      "201\n",
      "2021-01-18 00:00:00\n",
      "2021-11-17 00:00:00\n",
      "4\n",
      "MA201\n",
      "CZCE\n",
      "1m\n",
      "70200\n",
      "2021-01-15 21:00:00\n",
      "2021-11-24 10:14:00\n",
      "5\n",
      "MA888\n",
      "CZCE\n",
      "1m\n",
      "250065\n",
      "2018-11-16 21:00:00\n",
      "2021-11-22 14:59:00\n",
      "6\n",
      "RM201\n",
      "CZCE\n",
      "1m\n",
      "69795\n",
      "2021-01-15 21:00:00\n",
      "2021-11-23 09:14:00\n"
     ]
    }
   ],
   "source": [
    "cu.execute(\"select * from dbbaroverview\")\n",
    "for item in cu.fetchall():\n",
    "    for element in item:\n",
    "        print(element)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'EG2201', 'DCE', '1m', 67785, '2021-01-26 21:00:00', '2021-11-24 10:14:00')\n",
      "(2, 'EG2201', 'DCE', 'd', 194, '2021-01-27 00:00:00', '2021-11-17 00:00:00')\n",
      "(3, 'MA201', 'CZCE', 'd', 201, '2021-01-18 00:00:00', '2021-11-17 00:00:00')\n",
      "(4, 'MA201', 'CZCE', '1m', 70200, '2021-01-15 21:00:00', '2021-11-24 10:14:00')\n",
      "(5, 'MA888', 'CZCE', '1m', 250065, '2018-11-16 21:00:00', '2021-11-22 14:59:00')\n",
      "(6, 'RM201', 'CZCE', '1m', 69795, '2021-01-15 21:00:00', '2021-11-23 09:14:00')\n"
     ]
    }
   ],
   "source": [
    "cu.execute(\"select * from dbbaroverview\")\n",
    "for item in cu.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cu.execute(\"select * from dbtickdata where symbol = 'eg2201'\")\n",
    "for item in cu.fetchall():\n",
    "    print(item)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
